%include "I:\workdata\706727\Build\Directories_And_SampleRestrictions.sas";
%include "I:\workdata\706727\Build\Programs.sas";
libname educform "\\srvfsenas1\data\Formater\SAS formater i Danmarks Statistik\SAS_datasaet\Uddannelser";


data base (drop=befupdSourceYear);
set raw.befupdv (keep= pnr befupdSourceYear mor_id far_id FOED_DAG where=(befupdSourceyear>1989 and befupdSourceYear<2020 and befupdSourceyear-year(FOED_DAG)<=70 and befupdSourceyear-year(FOED_DAG)>=5));
year=BEFupdSourceYear;
length year 8;
birthmonth=month(FOED_DAG);
birthyear=year(FOED_DAG);
rename mor_id=pnrm;
rename far_id=pnrf;
run;

/* Income */

data ind;
set raw.indupd01v (keep=pnr indupd01sourceyear perindkialt_13 qaktivf_ny05 qpassivn kontanthj_13 loenmv_13 offpens);
rename indupd01sourceyear=year;
rename perindkialt_13=inc;
rename loenmv_13=earn;
rename qaktivf_ny05=assets;
rename qpassivn=liabilities;
wealth=qaktivf_ny05-qpassivn;
drop=liabilities;
run;

proc sort data=ind nodup; by pnr year; run;

data ind(drop=lagpnr lagyear);
set ind;
lagpnr=lag(pnr);
lagyear=lag(year);
if lagyear=year and lagpnr=pnr then delete;
run;

/* Education */
data udda;
set raw.uddaupd01v ;
educ=coalesce(udd,udd_n);
higheduc=coalesce(educ,hfaudd);
rename uddaupd01sourceyear=year;
run;

proc sort data=udda nodup;
by pnr year;
run;

data uformat;
merge educform.C_isced_audd_afsp4l_k educform.C_isced_udd_fsp4l_k;
by start;
run;

data format(drop=start isced_audd_afsp4l_k isced_udd_fsp4l_k);
set uformat;
higheduc=start*1;
isced=coalesce(isced_audd_afsp4l_k,isced_udd_fsp4l_k)*1;
run;

proc sort data=format nodup; by higheduc; run;

proc sql;
create table fudda(keep=pnr year isced hfaudd) as select * from udda as a

left join format as b on a.higheduc=b.higheduc;
quit;

proc sort data=fudda;
by pnr year;
run;

data fudda(keep=pnr year isced compeduc hfaudd);
set fudda;
if pnr=lag(pnr) then laghfaudd=lag(hfaudd);
compeduc=(laghfaudd~=hfaudd and hfaudd ne .);
run;


/* Occupation */
data idap;
set raw.idapupdv (keep=pnr idapupdsourceyear pstill arledgr PSOC_STATUS_KODE);
rename idapupdsourceyear=year;
run;


/* Merge data */
proc sql;
create table incdata as select * from base(keep=pnr pnrf pnrm year) as a

left join ind(drop=assets wealth) as b on a.pnr=b.pnr and a.year=b.year
left join ind(keep=pnr year inc earn assets wealth rename=(pnr=pnrf inc=incf earn=earnf assets=assetsf wealth=wealthf)) as c on a.pnrf=c.pnrf and a.year=c.year
left join ind(keep=pnr year inc earn assets wealth rename=(pnr=pnrm inc=incm earn=earnm assets=assetsm wealth=wealthm)) as d on a.pnrm=d.pnrm and a.year=d.year
left join idap as e on a.pnr=e.pnr and a.year=e.year
left join fudda as f on a.pnr=f.pnr and a.year=f.year;
quit;

proc export data=incdata
outfile = "I:\Workdata\706727\Build\Data\incdata.dta"
replace;
run;
